package com.gege.homework10;

import com.gege.homework10.model.User;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * 使用 JDBC 原生接口，实现数据库的增删改查操作
 *
 * @author gechenpeng
 * @date 2021-04-20
 */
public class JdbcDemo {

    private static final String url = "jdbc:mysql://localhost:3306/gogo?useUnicode=true&characterEncoding=utf-8&useSSL=false";

    private static final String username = "xiaogege";

    private static final String password = "imissyou";

    static {
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) throws Exception {
        User user = User.builder()
                .nickname("herrier")
                .phoneNumber("18021565656")
                .build();
        deleteUser("herrier");

        queryUsers().stream().forEach(u -> System.out.println(u.getNickname() + u.getPhoneNumber()));

    }

    public static int addUser(User user) throws SQLException {
        Connection connection = null;
        Statement statement = null;
        try {
            connection = getConnection();
            statement = connection.createStatement();
            String sql = "INSERT INTO user (id, nickname) " +
                    "VALUES ('" + user.getId() + "', '" + user.getNickname() + "')";
            return statement.executeUpdate(sql);
        } finally {
            close(null, statement, connection);
        }
    }

    public static int updateUser(User user) throws SQLException {
        Connection connection = null;
        Statement statement = null;
        try {
            connection = getConnection();
            statement = connection.createStatement();
            String sql = "UPDATE user SET phone_number = '" + user.getPhoneNumber() +
                    "' WHERE nickname = '" + user.getNickname() + "'";
            return statement.executeUpdate(sql);
        } finally {
            close(null, statement, connection);
        }
    }

    public static int deleteUser(String nickname) throws SQLException {
        Connection connection = null;
        Statement statement = null;
        try {
            connection = getConnection();
            statement = connection.createStatement();
            String sql = "DELETE FROM user WHERE nickname = '" + nickname + "'";
            return statement.executeUpdate(sql);
        } finally {
            close(null, statement, connection);
        }
    }

    private static void close(ResultSet resultSet, Statement statement, Connection connection) {
        try {
            if (null != resultSet) {
                resultSet.close();
            }

            if (null != statement) {
                statement.close();
            }

            if (null != connection) {
                connection.close();
            }
        } catch (SQLException e) {
            throw new RuntimeException(e.getMessage());
        }
    }

    public static List<User> queryUsers() throws SQLException {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            connection = getConnection();
            statement = connection.createStatement();
            String sql = "SELECT * FROM user";
            resultSet = statement.executeQuery(sql);
            List<User> userList = new ArrayList<>();
            while (resultSet.next()) {
                userList.add(User.builder()
                        .id(resultSet.getString("id"))
                        .nickname(resultSet.getString("nickname"))
                        .phoneNumber(resultSet.getString("phone_number"))
                        .build());
            }
            return userList;
        } finally {
            close(resultSet, statement, connection);
        }
    }

    private static Connection getConnection() {
        try {
            return DriverManager.getConnection(url, username, password);
        } catch (SQLException e) {
            throw new RuntimeException(e.getMessage());
        }
    }
}
